package com.alex.mapper;

import com.alex.entity.ShsqWater;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.Select;
import org.mapstruct.Mapper;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Mapper
@Repository
public interface ShQfqWaterMapper {

    //用 GROUP_CONCAT 的方式将排序的id连接成一个字符然后在group by stcd,再在结果中截取每条的第一个就是最新数据的 id
    /*@Select("SELECT sa.id, sa.stcd,sa.voltage,sc.pj,sc.pn05,sa.z,sa.tm,sb.stnm,sb.dsfl " +
            "from ${tabname} sa, st_stbprp_b sb, st_devicefactor sc where sa.id in " +
            "(SELECT SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY tm DESC),',',1) FROM ${tabname} GROUP BY stcd) " +
            "and sa.stcd = sb.stcd and sa.stcd = sc.stcd limit #{start}, #{num}")*/
    //
    @Select("SELECT b.stcd stcd,c.stnm,a.Voltage voltage, FORMAT(IFNULL(SUM(a.pn05),0) + (SELECT CASE WHEN DATE_FORMAT(b.tm,'%H:%i') <= '08:00' and DATE_FORMAT(NOW(),'%H:%i') > '08:05' THEN 0 ELSE b.pj END), 1) pj, " +
            "IFNULL(a.z,b.z) z,IFNULL(MAX(a.tm),b.tm) tm,c.dsfl dsfl,c.stlc stlc from ${tabname} a, (SELECT stcd, Voltage,pj,pn05,z,tm from ${tabname} " +
            "WHERE stcd = #{stcd} and pj is not NULL ORDER BY tm desc LIMIT 1) b, st_stbprp_b c " +
            "WHERE a.stcd = c.stcd and a.stcd = #{stcd} and a.tm > (SELECT CASE WHEN DATE_FORMAT(NOW(),'%H:%i') > '08:05' AND DATE_FORMAT(b.tm,'%H:%i') <= '08:00' " +
            "THEN FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(SYSDATE() AS DATE) + INTERVAL 8 HOUR)) ELSE b.tm END) ORDER BY a.tm DESC;")
    @ResultType(ShsqWater.class)
    ShsqWater getShsqWaters(@Param("tabname") String tabname, @Param("stcd") String stcd);

    @Select("SELECT count(1) from ${tabname} where id in (SELECT SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY tm DESC),',',1) FROM ${tabname} GROUP BY stcd)")
    @ResultType(Integer.class)
    Integer getShsqWaterstotal(@Param("tabname") String tabname);

    @Select("SELECT count(1) from st_stbprp_b")
    @ResultType(Integer.class)
    Integer getDevicestotal();

    /*@Select("SELECT sb.stcd,sa.voltage,sa.pj,sa.pn05,sa.z,sa.tm,sb.stnm,sb.dsfl, sb.stlc " +
            "from ${tabname} sa RIGHT JOIN st_stbprp_b sb on sa.id in " +
            "(SELECT SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY tm DESC),',',1) FROM ${tabname} GROUP BY stcd) " +
            "and sa.stcd = sb.stcd limit #{start}, #{num}")*/

    @Select("SELECT b.stcd stcd,c.stnm,a.Voltage voltage, FORMAT(IFNULL(SUM(a.pn05),0) + (SELECT CASE WHEN DATE_FORMAT(b.tm,'%H:%i') <= '08:00' and DATE_FORMAT(NOW(),'%H:%i') > '08:05' THEN 0 ELSE b.pj END), 1) pj, " +
            "IFNULL(a.z,b.z) z,IFNULL(MAX(a.tm),b.tm) tm,c.dsfl dsfl,c.stlc stlc from ${tabname} a, (SELECT stcd, Voltage,pj,pn05,z,tm from ${tabname} " +
            "WHERE stcd = #{stcd} and pj is not NULL ORDER BY tm desc LIMIT 1) b, st_stbprp_b c " +
            "WHERE a.stcd = c.stcd and a.stcd = #{stcd} and a.tm > (SELECT CASE WHEN DATE_FORMAT(NOW(),'%H:%i') > '08:05' AND DATE_FORMAT(b.tm,'%H:%i') <= '08:00' " +
            "THEN FROM_UNIXTIME(UNIX_TIMESTAMP(CAST(SYSDATE() AS DATE) + INTERVAL 8 HOUR)) ELSE b.tm END) ORDER BY a.tm DESC;")
    @ResultType(ShsqWater.class)
    ShsqWater getDevices(@Param("tabname") String tabname, @Param("stcd") String stcd);

    @Select("select count(1) from st_stbprp_b where dsfl = 1")
    @ResultType(Integer.class)
    Integer getOnlines();

    @Select("select count(1) from st_stbprp_b")
    @ResultType(Integer.class)
    Integer getTotals();

    @Select("select stcd from st_stbprp_b")
    @ResultType(String.class)
    List<String> getstcds();

}
